package com.jiazhong.basic.pack0524.manytoone.dao;

import com.jiazhong.basic.pack0524.db.DBManager;
import com.jiazhong.basic.pack0524.manytoone.bean.Dept;
import com.jiazhong.basic.pack0524.manytoone.bean.Emp;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class EmpDAO {

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    /**
     * 根据员工编号获取到该员工的信息(仅查询员工信息)
     *
     * @param 员工编号
     * @return 员工信息
     */
    @SneakyThrows
    public Emp findById1(int empNo) {
        conn = DBManager.getConnection();
        String sql = "select * from emp where state=1 and empno=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, empNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            Emp emp = new Emp();
            emp.setEmpNo(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString("job"));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble("comm"));
            emp.setDeptNo(rs.getInt(8));
            emp.setState(rs.getInt(9));
            return emp;
        }
        return null;
    }

    /**
     * 根据员工编号获取到该员工的信息（包含部门信息）
     *
     * @param empNo
     * @return 员工信息
     */
    @SneakyThrows
    public Emp findById2(int empNo) {
        conn = DBManager.getConnection();
        // 一次就要查出员工和部门信息
        String sql = "select * from emp e " +
                "left join dept d on d.deptno = e.deptno " +
                "where state=1 and empno=?";
        ps = conn.prepareStatement(sql);
        ps.setInt(1, empNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            // 将前9列赋予了Emp类的对象
            Emp emp = new Emp();
            emp.setEmpNo(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString("job"));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getString(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble("comm"));
            emp.setDeptNo(rs.getInt(8));
            emp.setState(rs.getInt(9));
            // 将10-12列赋予了部门信息
            Dept dept = new Dept();
            dept.setDeptNo(rs.getInt(10));
            dept.setDname(rs.getString(11));
            dept.setLoc(rs.getString(12));
            // 将dept信息存放到emp中
            emp.setDept(dept);
            return emp;
        }
        return null;
    }
}
